package handler;

import java.sql.*;
import java.util.ArrayList;

/**
 * Handles basic queries to the Database.  This class will connect and close connection
 * automatically so the user does not have too.  The Handler will all Creation, Updating,
 * and Selecting from the Database.
 * 
 * @author David Hietpas
 */
public class DBHandler {
    /**
     * A wrapper for basic operations such as connection management and
     * query execution
     */
    private DBConnector dbConnector;
    
    /**
     * Constructs a new DBHandler object using the default database
     * connection settings.
     */
    public DBHandler() {
        this.dbConnector = new DBConnector();
    }
    
    /**
     * Constructs a new DBHandler object. All queries will be performed with
     * the specified DBConnector object.
     * 
     * @param dbConnector object to use to perform queries
     */
    public DBHandler(DBConnector dbConnector) {
        this.dbConnector = dbConnector;
    }
    
    /**
     * Gets all Users from the DB.
     * 
     * @return ArrayList<User> 
     */
	public ArrayList<User> getAllUsers() {
		this.dbConnector.connect();
		ArrayList<User> al = new ArrayList<User>();
		ResultSet rs = dbConnector.execute("select * from users",new Object[]{});
		try {
    			while (rs.next()) 
    				al.add(new User(rs.getInt("id"),rs.getString("user"),rs.getString("password"),rs.getString("email"),
    				        rs.getBlob("public_key").getBytes(1, (int) rs.getBlob("public_key").length())));
			return al;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
		finally {
		    this.dbConnector.disconnect();
		}
	}
	
    /**
     * Gets a user by a users name.  Give the function a username
     * to look for it will return a list of results if found or
     * a empty list if not found.
     * 
     * @param String name
     * @return ArrayList<User> 
     */
	public ArrayList<User> getUsersByName(String name) {
		this.dbConnector.connect();
		ArrayList<User> al = new ArrayList<User>();
		ResultSet rs = dbConnector.execute("select * from users where user = ?",new Object[]{name});
		try {
    			while (rs.next()) 
    			    al.add(new User(rs.getInt("id"),rs.getString("user"),rs.getString("password"),rs.getString("email"),
                            rs.getBytes("public_key")));
            return al;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        finally {
            this.dbConnector.disconnect();
        }
	}
	
    /**
     * Gets a user by a users id.  Give the function a unique user id
     * to look for it will return a User if found or a
     * a empty User if not.
     * 
     * @param int id
     * @return User
     */
	public User getUserById(int id) {
		this.dbConnector.connect();
		User su = new User();
		ResultSet rs = dbConnector.execute("select * from users where id = ?",new Object[]{id});
		try {
		    if(rs.next())
    			su = new User(rs.getInt("id"),rs.getString("user"),rs.getString("password"),rs.getString("email"),
                                rs.getBlob("public_key").getBytes(1, (int) rs.getBlob("public_key").length()));
            return su;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        finally {
            this.dbConnector.disconnect();
        }
	}
	
    /**
     * Gets all Notebook Files.  Returns a list of files of type NotebookFile
     * 
     * @return ArrayList<NotebookFile>
     */
    public ArrayList<NotebookFile> getAllNotebookFiles() {
        this.dbConnector.connect();
        ArrayList<NotebookFile> al = new ArrayList<NotebookFile>();
        ResultSet rs = dbConnector.execute("select * from files",new Object[]{});
        try {
            while (rs.next()) 
                al.add(new NotebookFile(rs.getInt("id"),rs.getString("name"),
                        rs.getBlob("zip").getBytes(1, (int) rs.getBlob("zip").length()),
                        rs.getInt("owner"),rs.getInt("locked_by")));
            return al;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        finally {
            this.dbConnector.disconnect();
        }
    }
	
    /**
     * Gets a NotebookFile by a files name.  Give the function a file name
     * to look for it will return a list of NotebookFiles if found or a
     * empty list if not found
     * 
     * @param String name
     * @return ArrayList<NotebookFile> 
     */
	public ArrayList<NotebookFile> getNotebookFileByNotebookFileName(String name) {
		this.dbConnector.connect();
		ArrayList<NotebookFile> al = new ArrayList<NotebookFile>();
		ResultSet rs = dbConnector.execute("select * from files where name = ?",new Object[]{name});
		try {
			while (rs.next()) 
				al.add(new NotebookFile(rs.getInt("id"),rs.getString("name"),
                        rs.getBlob("zip").getBytes(1, (int) rs.getBlob("zip").length()),
                        rs.getInt("owner"),rs.getInt("locked_by")));
            return al;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        finally {
            this.dbConnector.disconnect();
        }
	}
	
    /**
     * Gets a NotebookFile by a files id.  Give the function a unique file id
     * to look for it will return a NotebookFile if found or a
     * empty NotebookFile if not found
     * 
     * @param int id
     * @return NotebookFile 
     */
	public NotebookFile getNotebookFileByNotebookFileId(int id) {
		this.dbConnector.connect();
		NotebookFile sf = null;
		ResultSet rs = dbConnector.execute("select * from files where id = ?",new Object[]{id});
		try {
		    if(rs.next())
    		    sf = new NotebookFile(rs.getInt("id"),rs.getString("name"),
                        rs.getBlob("zip").getBytes(1, (int) rs.getBlob("zip").length()),
                        rs.getInt("owner"),rs.getInt("locked_by"));
            return sf;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        finally {
            this.dbConnector.disconnect();
        }
	}

    /**
     * Gets a NotebookFile by a Users id.  Give the function a unique user id
     * to look for it will return a list of NotebookFiles if found or a
     * empty list of NotebookFiles if not found
     * 
     * @param int id
     * @return ArrayList<NotebookFile>  
     */
	public ArrayList<NotebookFile> getAllNotebookFilesFromUser(int id) {
        this.dbConnector.connect();
        ArrayList<NotebookFile> al = new ArrayList<NotebookFile>();
        ResultSet rs = dbConnector.execute("select * from files f, permissions p where f.id = p.file_id and p.user_id = ?",new Object[]{id});
        try {
                while (rs.next()) 
                    al.add(new NotebookFile(rs.getInt("id"),rs.getString("name"),
                            rs.getBlob("zip").getBytes(1, (int) rs.getBlob("zip").length()),
                            rs.getInt("owner"),rs.getInt("locked_by")));
             return al;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        finally {
            this.dbConnector.disconnect();
        }
	}
	
    /**
     * Gets a NotebookFile Permissions by a NotebookFile id and Users id.  
     * Give the function a unique user id and file id and it will return
     * the permissions for that user and file.
     * 
     * @param int id, Notebookfile id
     * @param int id, user id
     * @return Permissions
     */
	public Permissions getNotebookFilePermissions(int NotebookFileId, int userId) {
	    this.dbConnector.connect();
	    Permissions p = new Permissions();
        ResultSet rs = dbConnector.execute("select * from permissions where file_id = ? and user_id = ?",new Object[]{NotebookFileId,userId});
        try {
                while (rs.next()) 
                    p = new Permissions(rs.getInt("file_id"),rs.getInt("user_id"),rs.getInt("permission_type"),
                                            rs.getBlob("encrypted_des_key").getBytes(1, (int) rs.getBlob("encrypted_des_key").length()));
            return p;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
        finally {
            this.dbConnector.disconnect();
        }
	}
	
	
    /**
     * Adds new User to the DB.  Create a new User() and
     * pass it in as a argument.
     * 
     * @param User user
     */
	public void addNewUser(User user) {
		this.dbConnector.connect();
		dbConnector.execute("insert into users (user,password,email,public_key) values (?,?,?,?)", 
					new Object[]{user.getUserName(),user.getUserPassword(),user.getUserEmail(),user.getPublicKey()});
		this.dbConnector.disconnect();
	}
	
    /**
     * Adds new NotebookFile to the DB.  Create a new NotebookFile() and
     * pass it in as a argument.
     * 
     * @param NotebookFile notebookFile
     */
	public void addNewNotebookFile(NotebookFile notebookFile) {
		this.dbConnector.connect();
		dbConnector.execute("insert into files (name,zip,owner,locked_by) values (?,?,?,?)", 
					new Object[]{notebookFile.getNotebookFileName(),notebookFile.getNotebookFile(),notebookFile.getOwner(),notebookFile.getLockedByUserId()});
		this.dbConnector.disconnect();
	}
	
    /**
     * Adds new Permissions to the DB.  Create a new Permissions() and
     * pass it in as a argument.
     * 
     * @param Permissions perm
     */
    public void addNewPermission(Permissions perm) {
        this.dbConnector.connect();
        dbConnector.execute("insert into permissions (file_id,user_id,permission_type,encrypted_des_key) values (?,?,?,?)", 
                    new Object[]{perm.getNotebookFileId(),perm.getUserId(),perm.getPermissionType(),perm.getDesKey()});
        this.dbConnector.disconnect();
    }
	
    /**
     * Updates User in the DB.  Give a User to update, it will update the DB.
     * 
     * @param User user
     */
	public void updateUser(User user) {
		this.dbConnector.connect();
		dbConnector.execute("update users set user = ?, password = ?, public_key = ?, email = ? where id = ?", 
					new Object[]{user.getUserName(),user.getUserPassword(),user.getPublicKey(),user.getUserEmail(),user.getId()});
		this.dbConnector.disconnect();
	}
	
    /**
     * Updates NotebookFile in the DB.  Give a NotebookFile to update, it will update the DB.
     * 
     * @param NotebookFile notebookFile
     */
	public void updateNotebookFile(NotebookFile notebookFile) {
		this.dbConnector.connect();
		dbConnector.execute("update files set name = ?, zip = ?, owner = ?, locked_by = ? where id = ?", 
				new Object[]{notebookFile.getNotebookFileName(),notebookFile.getNotebookFile(),notebookFile.getOwner(),notebookFile.getLockedByUserId(),notebookFile.getId()});
		this.dbConnector.disconnect();
	}
	  
    /**
     * Updates Permissions in the DB.  Give a Permissions to update, it will update the DB.
     * 
     * @param Permissions perm
     */
    public void updatePermission(Permissions perm) {
        this.dbConnector.connect();
        dbConnector.execute("update permissions set permission_type = ?, encrypted_des_key = ? where file_id = ? and user_id = ?", 
                    new Object[]{perm.getPermissionType(),perm.getDesKey(),perm.getNotebookFileId(),perm.getUserId()});
        this.dbConnector.disconnect();
    }
        
    
}

